﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;

namespace Framework.DataAccess
{
    public class role
    {
        private readonly DbInstance db;
        public role()
        {
            db = DbManager.GetDbInstance();
        }

        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <returns></returns>
        public IList<role_table> GetRolist(bool excludeAdmin = false)
        {
            string strSql = "Select * From role_table Where 1=1";
            if (excludeAdmin)
                strSql += " And power<>1";
            IDataReader reader = db.ExecuteReaderBySql(strSql);
            IList<role_table> list = DbManager.FillList<role_table>(reader);
            if (!reader.IsClosed)
                reader.Close();
            return list;
        }

        public IList<role_table> GetRolistALL(bool excludeAdmin = false)
        {
            string strSql = "Select * From role_table Where 1=1";
            if (excludeAdmin)
                strSql += " And power<1";
            IDataReader reader = db.ExecuteReaderBySql(strSql);
            IList<role_table> list = DbManager.FillList<role_table>(reader);
            if (!reader.IsClosed)
                reader.Close();
            return list;
        }
        /// <summary>
        /// 获取角色列表 带分页
        /// </summary>
        /// <returns></returns>
        public PageList<object> GetRolist(PageView pageInfo, bool excludeAdmin = false)
        {
            string strWhere = "   Where 1=1";
            if (excludeAdmin)
                strWhere += " And power<>1";

            if (pageInfo.SearchParams["rolName"] != null)
            {
                strWhere += "  and [name] like '%" + pageInfo.SearchParams["rolName"].Replace("'", "") + "%'";
            }

            PageList<object> list = new PageList<object>();

            string strSql = "select count(*) as Row  from role_table  " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By  id ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where  rows Between {4} And {5}";


            strSql = string.Format(strSql, orderby, "*", "role_table", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }


        public DataTable GetRoleExcel(int areid, int status, string rolename)
        {
            string sql = " select  R_NAME as '职务名称', ";
            sql += " A_NAME as '区域名称', ";
            sql += " R_REMARK as '职务备注', ";
            sql += " CONVERT(varchar,R_DATE,120) as '职务创建日期', ";
            sql += " CASE(J_STATUS) when 0 then '停用' else '启用' end as 状态 ";
            sql += " from are_role_view   where R_POWER =0";
            sql += (areid > -1 ? " AND A_ID=" + areid : "");
            sql += (status == 0 || status == 1 ? " AND  J_Status=" + status : "");
            sql += (rolename.Length > 0 ? " AND R_NAME like '%" + rolename + "%'" : "");


            return db.ExecuteDataTableBySql(sql);
        }

        /// <summary>
        /// 根据区域获取 角色
        /// </summary>
        /// <param name="a_live"></param>
        /// <returns></returns>
        public PageList<object> GetRolist(int areid, int status, string rolename, PageView pageInfo)
        {

            string strWhere = "  where R_POWER =0";

            strWhere +=  (areid > -1? " AND A_ID=" + areid : "");
            strWhere +=  (status == 0 || status == 1? " AND  J_Status=" + status : "");
            strWhere +=  ((rolename!=null && rolename.Length>0)? " AND R_NAME like '%"+rolename+"%'": "");

            PageList<object> list = new PageList<object>();

            string strSql = "select count(*) as Row  from are_role_view  " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By  A_ID ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where  rows Between {4} And {5}";


            strSql = string.Format(strSql, orderby, "*", "are_role_view", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public int UpdateRole(areroleview Item)
        {
            string strSql = " if not EXISTS(select id from are_role_join where are={0} and [role]={1}) begin ";
                   strSql +=" update are_role_join set are={0},[role]={1} where id={2} ";
                   strSql += " select 1 end  else  select -99 ";

            strSql = string.Format(strSql,Item.A_ID,Item.R_ID, Item.J_ID);
            


            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = (int)db.ExecuteScalar(cmd);

            return R;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public int UpdateRoleNew(areroleview Item)
        {
            string strSql = "if not EXISTS(select id from role_table where name='{0}' and id<>{3}) ";
            strSql += " begin  update role_table set name='{0}',[power]={1},remark='{2}' where id={3}  select 1 ";
            strSql += " end  select -99";

            strSql = string.Format(strSql, Item.R_NAME.Replace("'", ""), Item.R_POWER, Item.R_REMARK.Replace("'", ""), Item.R_ID);



            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = (int)db.ExecuteScalar(cmd);

            return R;
        }
        /// <summary>
        /// 删除角色
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteRoleItem(int id)
        {

            string strSql = "if not EXISTS(select id from are_role_join where [role]={0}) ";
            strSql += " begin   delete from role_table where id={0}   select 1 ";
            strSql += " end  select -99";

            strSql = string.Format(strSql,id);



            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = (int)db.ExecuteScalar(cmd);

            return R;
        }

        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public int AddRoleNew(string name,string rmark)
        {
            //string strSql = "if not EXISTS(select id from role_table where name='{0}') ";
            //strSql += " begin  insert into role_table(name,[power],remark,[date])values('{0}',{1},'{2}',getdate())  select CONVERT(Int,SCOPE_IDENTITY()) ";
            //strSql += " end   else    select -99 ";

            int result = -1;

            string sql = "SELECT count(*) FROM role_table where [name] = '" + name.Trim()+"'";
            int count = db.ExecuteScalarBySql(sql).ToInt();
            if (count == 0)
            {
                sql = String.Format("insert role_table([name],[power],remark,[date]) values('{0}', 0,'{1}',getdate() ); Select Scope_Identity()As id ",
                    name.Trim(), rmark.Trim());
                DbCommand cmd = db.GetSqlStringCommand(sql);
                result = db.ExecuteScalar(cmd).ToInt();
            }
            else result = -99;

            return result;
        }

        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public int AddRoleNew(areroleview Item)
        {
            string strSql ="if not EXISTS(select id from role_table where name='{0}') ";
                   strSql +=" begin  insert into role_table(name,[power],remark,[date])values('{0}',{1},'{2}',getdate())  select CONVERT(Int,SCOPE_IDENTITY()) ";
                   strSql += " end   else    select -99 ";

             strSql = string.Format(strSql, Item.R_NAME.Replace("'", ""), Item.R_POWER, Item.R_REMARK);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = (int)db.ExecuteScalar(cmd);

            return R;
        }
        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public int AddRole(areroleview Item)
        {
            //string strSql = "declare @newRoleId int ";
            //strSql += " insert into role_table(name,[power],remark,[date])values('{0}',{1},'{2}',getdate()) ";
            //strSql += " select @newRoleId=CONVERT(Int,SCOPE_IDENTITY()) ";
            //strSql += " insert into are_role_join(are,[role])values({3},@newRoleId) ";

            string strSql = "if not EXISTS(select id from are_role_join where are={0} and [role]=(select id from role_table where [id]={1} and [power]={2})) begin ";
             strSql += "  insert into are_role_join(are,[role])values({0},{1})  select 1  end  else  select -99 ";



             strSql= string.Format(strSql,Item.A_ID,Item.R_ID,Item.R_POWER);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            int R = (int)db.ExecuteScalar(cmd);

            return R;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="areid"></param>
        /// <param name="Role"></param>
        /// <returns></returns>
        public int AddAreaRole(int areid,string[] Role)
        { 
            string strSql=" if not EXISTS(select id from are_role_join where are={0} and [role]={1}  and [status]=1) begin ";
            strSql += " insert into are_role_join(are,[role],[status])values({0},{1},1) end ";

            string tempR = "";
            for (int i = 0; i < Role.Length; i++)
            {
                tempR += "  " + string.Format(strSql, areid, Role[i]);
            }
            if (tempR.Length > 10)
            {
                DbCommand cmd = db.GetSqlStringCommand(tempR);
                return db.ExecuteNonQuery(cmd);
            }
            else
                return 0;

        
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="staus"></param>
        /// <param name="jid"></param>
        /// <returns></returns>
        public int stopItem(int staus, int jid)
        {
            string strSql = "update  are_role_join set [status]=" + staus + " where id="+jid;

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            return db.ExecuteNonQuery(cmd);
        
        }

        /// <summary>
        /// 提权操作
        /// </summary>
        /// <param name="memberid"></param>
        /// <param name="areid"></param>
        /// <returns></returns>
        public bool PromotePower(int memberid )
        {
            bool result = false;
            string sql ="select are_role_view.J_ID from are_role_view ";
                sql +=" join member_view on member_view.A_ID = are_role_view.A_ID ";
                sql += " where  are_role_view.R_POWER =1 AND member_view.M_ID =" + memberid;


                int areroleid = db.ExecuteScalarBySql(sql).ToInt();
                sql = String.Format("update user_join set are_role = {0} where member={1}", areroleid, memberid);

                areroleid = db.ExecuteNonQueryBySql(sql);
                if (areroleid > 0)
                    result = true;

            

            return result;
        }

    }
}
